<template>
  <el-dialog v-el-drag-dialog title="查询导出" :visible.sync="visible" :append-to-body="appendToBody" :before-close="onClose">
    <el-form ref="dbForm" label-width="80px">
      <el-row>
        <el-form-item label="数据源" prop="dbKey">
          <el-select
            v-model="dbKey"
            v-el-select-load-more="loadMore"
            filterable
            remote
            reserve-keyword
            placeholder="请输入数据源名称"
            :remote-method="onSearch"
            :loading="loading"
            @change="getDbInfo"
          >
            <el-option
              v-for="item in dbData"
              :key="item.id"
              :label="item.text"
              :value="item.id"
            />
          </el-select>
          <el-button class="fr p-r-10" type="primary" round @click="addQuery">添加查询语句</el-button>
        </el-form-item>
      </el-row>
      <el-row>
        <el-form-item label="自定义查询语句" prop="groupName">
          <el-select
            v-model="groupName"
            v-el-select-load-more="loadMoreCustomQuery"
            filterable
            remote
            reserve-keyword
            placeholder="请输入所属分组名称"
            :remote-method="onSearchCustomQuery"
            :loading="loadingCustomQuery"
          >
            <el-option
              v-for="item in customQueryData"
              :key="item.id"
              :label="item.text"
              :value="item.id"
            />
          </el-select>
          <el-button v-if="groupName" class="fr m-r-10" type="danger" round @click="deleteCustomQuery">删除</el-button>
          <el-button v-if="groupName" class="fr m-r-10" type="primary" round @click="loadCustomQuery">加载</el-button>
        </el-form-item>
      </el-row>
      <el-row>
        <el-card class="box-card m-b-5" shadow="always">
          <codemirror ref="sqlExecutorEditor" v-model="curQuery" class="code-mirror" :options="cmOption" @ready="onCmReady" @input="onCmCodeChange" />
          <el-button v-if="sqlExecuting" type="primary" plain icon="el-icon-video-pause" @click="cancelExecutorSql">取消执行</el-button>
          <el-tooltip v-else effect="dark" content="Ctrl+R、Ctrl+Enter" placement="top">
            <el-button type="primary" plain icon="el-icon-video-play" @click="doExecutorSql">执行</el-button>
          </el-tooltip>
          <el-button icon="el-icon-brush" @click="formatterSql">SQL美化</el-button>
        </el-card>
      </el-row>
      <el-divider />
      <el-row>
        <el-card class="box-card m-b-5" shadow="always">
          <div v-if="!!executeError" style="color: #f00;">{{ executeError }}</div>
          <div v-else-if="executeResultList.length <= 0" v-loading="sqlExecuting">暂无数据</div>
          <div v-else>
            <el-tabs :value="executeShowTable">
              <el-tab-pane label="信息" name="table0">
                <pre>{{ executeResultInfo }}</pre>
              </el-tab-pane>
              <el-tab-pane v-for="(resultItem, index) in executeResultList" :key="'ResultItem_' + index" :label="'结果' + resultItem.index" :name="'table'+resultItem.index">
                <div v-if="!!resultItem.errMsg" style="color: #f00;">{{ resultItem.errMsg }}</div>
                <el-table v-else :data="resultItem.dataList" stripe border class="execute-result-table m-b-5 w-100" max-height="600">
                  <el-table-column v-if="resultItem.dataCols.length > 0" width="60px">
                    <template slot-scope="scope">{{ scope.row._index }}</template>
                  </el-table-column>
                  <el-table-column v-for="(item, index2) in resultItem.dataCols" :key="'Item_' + index2" :prop="item.prop" :label="item.prop" :width="item.width">
                    <template slot-scope="scope">
                      <span :title="scope.row[item.prop]">{{ scope.row[item.prop] | ellipsis }}</span>
                    </template>
                  </el-table-column>
                </el-table>
              </el-tab-pane>
            </el-tabs>
          </div>
        </el-card>
      </el-row>
      <el-divider />
      <el-row>
        <el-card>
          <el-form-item v-if="querys.length > 0 && dbKey">
            <el-row :gutter="10">
              <el-col :span="1.5">
                <el-upload
                  ref="upload"
                  with-credentials
                  :headers="headers"
                  :limit="1"
                  accept=".xls, .xlsx"
                  :file-list="excelFile"
                  :auto-upload="false"
                  :action="uploadAction"
                  :on-change="handleChangeExcel"
                  :on-success="handleUploadExcel"
                  :on-error="handleUploadException"
                  class="upload-demo">
                  <el-button slot="trigger" size="small" type="primary">选取Excel文件</el-button>
                </el-upload>
              </el-col>
              <el-col :span="1.5" class="fr">
                <el-link
                  :underline="false"
                  icon="el-icon-document-copy"
                  v-clipboard:copy="'{result:*:in}'"
                  style="float:left; margin-right: 20px;"
                >
                  占位符{result:*:in}
                </el-link>
                <el-link
                  :underline="false"
                  icon="el-icon-document-copy"
                  v-clipboard:copy="'{excel:*:in}'"
                  style="float:right"
                >
                  占位符{excel:*:in}
                </el-link>
              </el-col>
            </el-row>
          </el-form-item>
          <el-form-item v-for="(query, index) in querys" :key="index" :label="'查询步骤' + (index + 1)" :inline="true">
            <el-input v-model="query.sql" type="textarea" style="width:85%" :rows="5" />
            <el-button type="danger" round @click="removeQuery(index)">删除</el-button>
          </el-form-item>
        </el-card>
      </el-row>
    </el-form>
    <span id="widthCalculate" style="visibility: hidden; white-space: nowrap;position: fixed;" />
    <span slot="footer" class="dialog-footer">
      <el-button type="success" icon="el-icon-check" :disabled="querys.length < 1 || !dbKey" @click="saveCustomQuery">保存查询统计</el-button>
      <el-button type="primary" icon="el-icon-check" :disabled="querys.length < 1" @click="onSubmit">导出结果</el-button>
      <el-button icon="el-icon-close" @click="onClose">关 闭</el-button>
    </span>
  </el-dialog>
</template>

<script>
import { dbaseSelectData, customQuerySelectData, customQueryLoad, customQuerySave, customQueryDelete, dbaseExecute, dbaseCancel, dbaseExport } from '@/api/tool/dbase'
import sqlFormatter from 'sql-formatter'
import { codemirror } from 'vue-codemirror'
import 'codemirror/mode/javascript/javascript'
import 'codemirror/mode/sql/sql.js'
import { getAuthenticationHeader } from '@/utils/store'
import { getDatabaseInfo, searchTable, searchColumn } from './setting.js'
import './setting.js'

export default {
  name: 'DbQueryDialog',
  directives: {
    'el-select-load-more': {
      bind(el, binding) {
        // 获取element-ui定义好的scroll盒子
        const SELECT_WRAP_DOM = el.querySelector('.el-select-dropdown .el-select-dropdown__wrap')
        SELECT_WRAP_DOM.addEventListener('scroll', function() {
          /**
            * scrollHeight 获取元素内容高度(只读)
            * scrollTop 获取或者设置元素的偏移值,常用于, 计算滚动条的位置, 当一个元素的容器没有产生垂直方向的滚动条, 那它的scrollTop的值默认为0.
            * clientHeight 读取元素的可见高度(只读)
            * 如果元素滚动到底, 下面等式返回true, 没有则返回false:
            * ele.scrollHeight - ele.scrollTop === ele.clientHeight;
            */
          const condition = this.scrollHeight - this.scrollTop <= this.clientHeight
          if (condition) {
            binding.value()
          }
        })
      }
    }
  },
  components: { codemirror },
  data() {
    return {
      visible: false,
      appendToBody: false,
      hasLoaded: false,
      headers: getAuthenticationHeader(),
      dbKey: '',
      term: undefined,
      loading: false,
      page: 1,
      length: 20,
      more: false,
      dbData: [],
      groupName: undefined,
      customQueryTerm: undefined,
      loadingCustomQuery: false,
      customQueryPage: 1,
      customQueryLength: 20,
      customQueryMore: false,
      customQueryData: [],
      querys: [],
      excelFile: [],
      curQuery: '',
      curTable: '',
      notifyTip: undefined,
      sqlExecuting: false,
      nowExecutorId: 1,
      executeResultInfo: '',
      executeShowTable: 'table1',
      executeResultList: [],
      executeError: '',
      executeUseTime: '',
      cmOption: {
        tabSize: 2, // tab空格数
        styleActiveLine: true, // 高亮选中行
        lineNumbers: true, // 显示行号
        styleSelectedText: true,
        line: true,
        foldGutter: false, // 块槽
        gutters: ['CodeMirror-linenumbers', 'CodeMirror-foldgutter'],
        // highlightSelectionMatches: { showToken: /\w/, annotateScrollbar: true }, // 可以启用该选项来突出显示当前选中的内容的所有实例
        mode: { // 模式, 可查看 codemirror/mode 中的所有模式
          name: 'sql'
        },
        hintOptions: {
          hint: this.onCmShowHint,
          // 当匹配只有一项的时候是否自动补全
          completeSingle: false
        },
        // 快捷键 可提供三种模式 sublime、emacs、vim
        // keyMap: 'sublime',
        matchBrackets: true,
        showCursorWhenSelecting: true,
        theme: 'monokai', // 主题
        scrollbarStyle: 'null',
        extraKeys: { 'Tab': 'autocomplete' }
      }
    }
  },
  computed: {
    uploadAction: function() {
      return this.$store.state.app.server_api + `${process.env.VUE_APP_MODULE_ADMIN_API}/public/file/upload`
    }
  },
  methods: {
    init() {
      this.visible = true
    },
    getDbInfo(dbKey) {
      getDatabaseInfo(dbKey)
    },
    onCmReady() {
      this.$refs.sqlExecutorEditor.codemirror.setSize('auto', '200px')
    },
    onCmShowHint() {
      const instance = this.$refs.sqlExecutorEditor.codemirror
      const cur = instance.getCursor()
      if (!this.dbKey) {
        return { list: list, from: { line: cur.line, ch: 0 }, to: { line: cur.line, ch: 0 }}
      }
      const curLine = instance.getLine(cur.line)
      const end = cur.ch
      let list = []

      // 根据不同情况给list赋值，默认为[]，即不显示提示框。
      const cursorTwoCharactersBefore = `${curLine.charAt(end - 1)}${curLine.charAt(end)}`
      if (curLine.endsWith('from ') || curLine.endsWith('join ')) {
        list = searchTable(this.dbKey, null)
      } if (cursorTwoCharactersBefore === '.') {
        const regx = /from\s+\w+/ig
        const keyword = regx.exec(curLine)
        if (keyword && keyword[0]) {
          this.curTable = keyword[0].replace('from ', '')
        }
        list = searchColumn(this.curTable, null)
      }
      return { list: list, from: { line: cur.line, ch: end }, to: { line: cur.line, ch: end }}
    },
    onCmCodeChange(value) {
      if (this.dbKey) {
        this.$refs.sqlExecutorEditor.codemirror.closeHint()
        this.$refs.sqlExecutorEditor.codemirror.showHint()
      }
    },
    onSearch(query) {
      this.loading = true
      this.page = 1
      this.term = query
      this.dbData = []
      this.getDbData()
      this.page++
      this.loading = false
      this.$modal.notifySuccess('按空格进行代码提示')
    },
    loadMore() {
      if (this.more) {
        this.page++
        this.getDbData()
      }
    },
    getDbData() {
      dbaseSelectData({
        term: this.term, // 输入值
        page: this.page, // 页码
        length: this.length
      }).then(response => {
        this.dbData = [...this.dbData, ...response.param.data]
        this.more = (this.page * this.length) < response.param.total
      }).catch(e => {})
    },
    onSearchCustomQuery(query) {
      this.loadingCustomQuery = true
      this.customQueryPage = 1
      this.customQueryTerm = query
      this.customQueryData = []
      this.getCustomQueryData()
      this.customQueryPage++
      this.loadingCustomQuery = false
    },
    loadMoreCustomQuery() {
      if (this.customQueryMore) {
        this.customQueryPage++
        this.getCustomQueryData()
      }
    },
    getCustomQueryData() {
      customQuerySelectData({
        term: this.customQueryTerm, // 输入值
        page: this.customQueryPage, // 页码
        length: this.customQueryLength
      }).then(response => {
        this.customQueryData = [...this.customQueryData, ...response.param.data]
        this.customQueryMore = (this.customQueryPage * this.customQueryLength) < response.param.total
      }).catch(e => {})
    },
    loadCustomQuery() {
      customQueryLoad({ groupName: this.groupName }).then(response => {
        this.querys = []
        response.param.forEach(query => {
          this.dbKey = query.dbKey
          this.querys.push({
            sql: query.sql
          })
        })
      }).catch(e => {})
    },
    saveCustomQuery() {
      const sqlValue = this.$refs.sqlExecutorEditor.codemirror.getValue()
      let param = {
        dbKey: this.dbKey,
        index: this.querys.length,
        sql: sqlValue
      }
      this.querys.map((query, index) => {
        param['querySql_' + (index + 1)] = query.sql
      })
      this.$prompt('请输入所属分组名称', '保存自定义查询统计', {
        confirmButtonText: '确定',
        cancelButtonText: '取消'
      }).then(({ value }) => {
        param['groupName'] = value
        customQuerySave(param).then(response => {
          this.$modal.msgSuccess(response.msg)
        })
      }).catch(() => {})
    },
    deleteCustomQuery() {
      customQueryDelete({ groupName: this.groupName }).then(response => {
        this.$modal.msgSuccess(response.msg)
      })
    },
    formatterSql() {
      let sqlValue = this.$refs.sqlExecutorEditor.codemirror.getValue()
      this.$refs.sqlExecutorEditor.codemirror.setValue('')
      if (sqlValue) {
        sqlValue = sqlFormatter.format(sqlValue)
        this.$refs.sqlExecutorEditor.codemirror.setValue(sqlValue)
      }
    },
    doExecutorSql() {
      if (this.dbKey === undefined) {
        this.$modal.msgWarning('请先选择数据源')
        return
      }
      this.executeError = ''
      this.executeUseTime = ''
      this.executeResultList = []

      this.nowExecutorId = (new Date()).getTime() + Math.ceil(Math.random() * 1000)

      const sqlValue = this.$refs.sqlExecutorEditor.codemirror.getValue()
      this.sqlExecuting = true
      dbaseExecute(this.nowExecutorId, {
        dbKey: this.dbKey,
        sql: sqlValue
      }).then(response => {
        this.sqlExecuting = false
        const resultList = response.param || []
        const executeResultList = []
        let executeResultInfo = ''
        let itemIndex = 1
        resultList.forEach(result => {
          executeResultInfo += this.getExecuteInfo(result)
          const resultItem = this.dealExecuteResult(result)
          if (resultItem.updateCount < 0) {
            resultItem.index = itemIndex
            itemIndex++
          }
          executeResultList.push(resultItem)
        })
        this.executeShowTable = (itemIndex === 1) ? 'table0' : 'table1'
        this.executeResultInfo = executeResultInfo
        this.executeResultList = executeResultList
      }).catch(e => {
        this.sqlExecuting = false
        this.executeError = e.message
      })
    },
    cancelExecutorSql() {
      dbaseCancel(this.nowExecutorId)
    },
    getExecuteInfo(resultData) {
      let resultStr = resultData.sql
      resultStr += '\n> 状态：' + ((resultData.errMsg) ? 'ERROR' : 'OK')
      if (resultData.updateCount >= 0) {
        resultStr += '\n> 影响行数：' + resultData.updateCount
      }
      resultStr += '\n> 耗时：' + (resultData.useTime || 0) / 1000 + 's'
      resultStr += '\n\n'
      return resultStr
    },
    dealExecuteResult(resultData) {
      const dataList = resultData.result || []
      const executeResultCols = []
      if (dataList.length > 0) {
        const propData = dataList[0]
        for (const key in propData) {
          // 动态计算宽度
          document.getElementById('widthCalculate').innerText = key
          const width1 = document.getElementById('widthCalculate').offsetWidth
          document.getElementById('widthCalculate').innerText = propData[key]
          const width2 = document.getElementById('widthCalculate').offsetWidth
          let width = (width1 > width2) ? width1 : width2
          width = (width < 50) ? 50 : width
          width = (width > 200) ? 200 : width
          executeResultCols.push({ prop: key, width: width + 25 })
        }
        for (let i = 0; i < dataList.length; i++) {
          dataList[i]._index = i + 1
        }
      }
      const resultObj = {}
      resultObj.dataList = dataList
      resultObj.dataCols = executeResultCols
      resultObj.useTime = resultData.useTime || 0
      resultObj.errMsg = resultData.errMsg || ''
      resultObj.updateCount = resultData.updateCount
      return resultObj
    },
    addQuery() {
      const sqlValue = this.$refs.sqlExecutorEditor.codemirror.getValue()
      if (!this.notifyTip) {
        this.notifyTip = this.$notify({
          title: '添加查询语句说明',
          message: '{result:列名称:in},{result:列名称:eq}表示占位符,{excel:列表头:in}表示文件占位符',
          onClose: () => { this.notifyTip = undefined },
          duration: 5000
        })
      }

      this.querys.push({
        sql: sqlValue
      })
    },
    removeQuery(index) {
      this.querys.splice(index, 1)
    },
    handleChangeExcel(file, fileList) {
      this.excelFile = fileList
    },
    handleUploadExcel(response, file, fileList) {
      this.$refs.upload.clearFiles()
      this.doExport({
        dbKey: this.dbKey,
        index: this.querys.length,
        fileId: response.param
      })
    },
    handleUploadException(e) {
      this.$modal.msgError('上传文件失败')
      this.$modal.closeLoading()
    },
    onSubmit() {
      // eslint-disable-next-line prefer-const
      let param = {
        dbKey: this.dbKey,
        index: this.querys.length
      }
      if (this.dbKey && this.querys.length > 0) {
        this.$modal.loading('执行中...')

        if (this.excelFile.length > 0) {
          this.$refs.upload.submit()
        } else {
          this.doExport(param)
        }
      } else {
        this.$modal.msgWarning('请选择数据源并添加查询语句')
      }
    },
    doExport(param) {
      this.excelFile = []
      this.querys.map((query, index) => {
        param['querySql_' + (index + 1)] = query.sql
      })

      dbaseExport(param).then(response => {
        window.location.href = this.$store.state.app.server_api + `${process.env.VUE_APP_MODULE_ADMIN_API}/public/file/download?path=` + encodeURI(response.param) + '&delete=' + true
        this.$modal.closeLoading()
      }).catch(() => {
        this.$modal.closeLoading()
      })
    },
    onClose() {
      if (this.notifyTip) {
        this.notifyTip.close()
      }
      this.notifyTip = undefined
      this.dbKey = ''
      this.term = undefined
      this.loading = false
      this.page = 1
      this.more = false
      this.dbData = []
      this.querys = []
      this.curQuery = ''
      this.curTable = ''
      this.sqlExecuting = false
      this.nowExecutorId = 1
      this.executeResultInfo = ''
      this.executeShowTable = 'table1'
      this.executeResultList = []
      this.executeError = ''
      this.executeUseTime = ''
      this.visible = false
    }
  }
}
</script>

<style>
.CodeMirror-scroll {
  overflow: scroll !important;
  margin-bottom: 0;
  margin-right: 0;
  padding-bottom: 0;
  height: 100%;
  outline: none;
  position: relative;
  border: 1px solid #dddddd;
}
.CodeMirror-hints {
  z-index: 9999 !important;
}
</style>
<style lang="less" scoped>
.code-mirror {
  font-size : 13px;
  line-height : 150%;
  text-align: left;
}
.pre {
  overflow-x: auto;
}
</style>
